package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.model.good.Good;

/**
 * 商品dao层
 * @author Administrator
 *
 */
public class GoodDao {
	private StringBuffer sql = null;
	SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	/**
	 * 增加商品 
	 * @param nGood
	 * @return
	 * @throws Exception
	 */
	public int addGood(Good nGood) throws Exception{
		sql = new StringBuffer("");
		sql.append("insert into t_goods_info(goodname,score,price,memberprice,num,picurl,addate)values('")
		.append(nGood.getGoodname()).append("',")
		.append(nGood.getScore()).append(",")
		.append(nGood.getPrice()).append(",")
		.append(nGood.getMemberprice()).append(",")
		.append(nGood.getNum()).append(",'")
		.append(nGood.getPicurl()).append("','")
		.append(df.format(new Date())).append("');");
		
		int iFlag = DBHelper.updateData(sql.toString());
		return iFlag;	
	}
	
	/**
	 * 修改商品
	 * @param uGood
	 * @return
	 * @throws Exception
	 */
	public int updateGood(Good uGood) throws Exception{
		sql = new StringBuffer("");
		sql.append("update t_goods_info set goodname='").append(uGood.getGoodname())
		.append("',score=").append(uGood.getScore())
		.append(",price=").append(uGood.getPrice())
		.append(",memberprice=").append(uGood.getMemberprice())
		.append(",solded=").append(uGood.getSolded())
		.append(",num=").append(uGood.getNum())
		.append("' where id=").append(uGood.getId()).append(";");
			
		int iFlag = DBHelper.updateData(sql.toString());
		return iFlag;	
	}
	
	/**
	 * 删除商品
	 * @param dGood
	 * @return
	 * @throws Exception
	 */
	public int deleteGood(Good dGood) throws Exception{
		sql = new StringBuffer("");
		sql.append("delete from t_goods_info ").append(" where id=").append(dGood.getId()).append(";");
		
		int iFlag = DBHelper.updateData(sql.toString());	
		return iFlag;		
	}
	
	/**
	 * 查询所有的商品
	 * @return
	 * @throws Exception
	 */
	public List<Good> findAll() throws Exception{
		sql = new StringBuffer("");
		sql.append("select * from t_goods_info");
		ResultSet rs = DBHelper.queryData(sql.toString());

		List<Good> GoodList = new ArrayList<Good>();
		while(rs.next()){
			Good g = new Good();
			
			g.setId(rs.getLong("id"));
			g.setGoodname(rs.getString("goodname"));
			g.setScore(rs.getInt("score"));
			g.setPrice(rs.getDouble("price"));
			g.setMemberprice(rs.getDouble("memberprice"));
			g.setSolded(rs.getInt("solded"));
			g.setAddate(rs.getDate("addate"));
			g.setNum(rs.getInt("num"));
			g.setPicurl(rs.getString("picurl"));
			
			GoodList.add(g);
		}	
		return GoodList;	
	}
	
	
	/**
	 * 查询最新商品的前12个商品
	 * @return
	 * @throws Exception
	 */
	public List<Good> findTwelve() throws Exception{
		sql = new StringBuffer("");
		sql.append("SELECT * FROM t_goods_info ORDER BY addate DESC LIMIT 12;");
		ResultSet rs = DBHelper.queryData(sql.toString());

		List<Good> GoodList = new ArrayList<Good>();
		while(rs.next()){
			Good g = new Good();
			
			g.setId(rs.getLong("id"));
			g.setGoodname(rs.getString("goodname"));
			g.setScore(rs.getInt("score"));
			g.setPrice(rs.getDouble("price"));
			g.setMemberprice(rs.getDouble("memberprice"));
			g.setSolded(rs.getInt("solded"));
			g.setAddate(rs.getDate("addate"));
			g.setNum(rs.getInt("num"));
			g.setPicurl(rs.getString("picurl"));
			
			GoodList.add(g);
		}	
		return GoodList;	
	}
	
	
	/**
	 * 查询一个商品
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Good findById(Long id) throws Exception{
		sql = new StringBuffer("");
		sql.append("select * from t_goods_info where id=?");	
		
		PreparedStatement pstmt = DBHelper.getConn().prepareStatement(sql.toString());
		pstmt.setLong(1, id);
		
		ResultSet rs=pstmt.executeQuery();
		if(!rs.next()){
			return null;
		}
			
		Good good = new Good();
		good.setId(rs.getLong("id"));
		good.setGoodname(rs.getString("goodname"));
		good.setScore(rs.getInt("score"));
		good.setPrice(rs.getDouble("price"));
		good.setMemberprice(rs.getDouble("memberprice"));
		good.setSolded(rs.getInt("solded"));
		good.setAddate(rs.getDate("addate"));
		good.setNum(rs.getInt("num"));
		good.setPicurl(rs.getString("picurl"));
			
		rs.close();
		pstmt.close();
	
		return good;
	}
}
